﻿
CREATE TABLE `addresses` (
  `AddressId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PersonId` int(10) unsigned NOT NULL,
  `Line1` varchar(50) NOT NULL,
  `Line2` varchar(50) DEFAULT NULL,
  `City` varchar(50) NOT NULL,
  `State` char(2) NOT NULL,
  `ZipCode` char(5) NOT NULL,
  `ZipCodePlusFour` char(4) DEFAULT NULL,
  PRIMARY KEY (`AddressId`),
  KEY `FK_addresses_person` (`PersonId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `companies` (
  `CompanyId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CompanyName` varchar(50) NOT NULL,
  PRIMARY KEY (`CompanyId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `people` (
  `PersonId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(50) NOT NULL,
  `LastName` varchar(50) NOT NULL,
  `Email` varchar(128) NOT NULL,
  `CompanyId` int(10) unsigned DEFAULT NULL,
  `RoleId` int(10) unsigned NOT NULL,
  `ManagerPersonId` int(10) unsigned DEFAULT NULL,
  `UserType` char(2) NOT NULL,
  PRIMARY KEY (`PersonId`),
  KEY `FK_people_role` (`RoleId`),
  KEY `FK_people_manager` (`ManagerPersonId`),
  KEY `FK_people_company` (`CompanyId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `portalpeoplexref` (
  `PortalId` int(10) unsigned NOT NULL,
  `PersonId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`PortalId`,`PersonId`),
  KEY `FK_pp_portal` (`PortalId`),
  KEY `FK_pp_people` (`PersonId`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE `portals` (
  `PortalId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PortalName` varchar(50) NOT NULL,
  `AdminUserId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`PortalId`),
  KEY `FK_portals_person` (`AdminUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `roles` (
  `RoleId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `RoleName` varchar(50) NOT NULL,
  `IsForPortal` tinyint(1) NOT NULL,
  `IsForContact` tinyint(1) NOT NULL,
  PRIMARY KEY (`RoleId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `portals` add
  CONSTRAINT `FK_portals_person` FOREIGN KEY (`AdminUserId`) REFERENCES `people` (`PersonId`);


ALTER TABLE `portalpeoplexref` add
  CONSTRAINT `FK_pp_portal` FOREIGN KEY (`PersonId`) REFERENCES `people` (`PersonId`);

ALTER TABLE `portalpeoplexref` add
  CONSTRAINT `FK_pp_people` FOREIGN KEY (`PortalId`) REFERENCES `portals` (`PortalId`);


ALTER TABLE `people` add CONSTRAINT `FK_people_manager` FOREIGN KEY (`ManagerPersonId`) REFERENCES `people` (`PersonId`);
ALTER TABLE `people` add CONSTRAINT `FK_people_companies` FOREIGN KEY (`PersonId`) REFERENCES `people` (`PersonId`);
ALTER TABLE `people` add CONSTRAINT `FK_people_role` FOREIGN KEY (`RoleId`) REFERENCES `roles` (`RoleId`);


ALTER TABLE `addresses` ADD  CONSTRAINT `FK_addresses_person` FOREIGN KEY (`PersonId`) REFERENCES `people` (`PersonId`);
